package cn.doitedu.rtmk.web;

import org.junit.Test;
import org.roaringbitmap.longlong.Roaring64Bitmap;
import redis.clients.jedis.Jedis;

import java.io.ByteArrayOutputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Arrays;
import java.util.HashSet;

/**
 * @Author: 深似海
 * @Site: <a href="www.51doit.com">多易教育</a>
 * @QQ: 657270652
 * @Date: 2023/12/24
 * @Desc: 学大数据，上多易教育
 * 规则管理平台的后端模拟器
 **/
public class RuleServiceV7 {

    @Test
    public void insertModelA_rule001() throws IOException, SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://doitedu:3306/doit43", "root", "root");

        // 从前端页面收到营销人员定义的规则参数
        String ruleParamJson = "{\n" +
                "  \"rule_id\": \"rule-A-001\",\n" +
                "  \"dynamic_profile_condition1\": {\n" +
                "    \"event_id\": \"R\",\n" +
                "    \"operator\": \">\",\n" +
                "    \"count\": 6\n" +
                "  },\n" +
                "  \"dynamic_profile_condition2\": {\n" +
                "    \"event_id\": \"K\",\n" +
                "    \"property_name\": \"p2\",\n" +
                "    \"operator\": \">\",\n" +
                "    \"max_min_bs\": 8\n" +
                "  },\n" +
                "  \"trigger_condition\": {\n" +
                "    \"event_id\": \"M\",\n" +
                "    \"property_name\": \"p1\",\n" +
                "    \"operator\": \">\",\n" +
                "    \"compare_value\": 2\n" +
                "  }\n" +
                "}";

        // 查询es，圈选出满足规则静态画像条件的人群
        // 假装查完了，得到如下满足条件的用户
        HashSet<Long> preSelectUsers = new HashSet<>(Arrays.asList(1L, 3L, 5L, 6L, 8L, 9L));
        Roaring64Bitmap bitmap = Roaring64Bitmap.bitmapOf();
        for (Long uid : preSelectUsers) {
            bitmap.add(uid);
        }
        // 序列化bitmap成byte[]
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        DataOutputStream dataOutputStream = new DataOutputStream(byteArrayOutputStream);
        bitmap.serialize(dataOutputStream);
        byte[] bitmapBytes = byteArrayOutputStream.toByteArray();


        // 查询该规则对应的模型运算机源代码
        PreparedStatement pst = connection.prepareStatement("select calculator_source_code from model_meta where model_id = ?");
        pst.setString(1, "A");
        ResultSet resultSet = pst.executeQuery();
        resultSet.next();
        String calculatorSourceCode = resultSet.getString("calculator_source_code");


        // 生成规则元数据，写入规则元数据表

        // 准备sql模板
        PreparedStatement preparedStatement = connection.prepareStatement("insert into rule_meta values (?,?,?,?,?,?,?)");
        // 填充value
        preparedStatement.setString(1, "rule-A-001");
        preparedStatement.setString(2, "A");
        preparedStatement.setString(3, "注册促单");
        preparedStatement.setString(4, "新用户注册后，用优惠券诱骗它下单");
        preparedStatement.setString(5, ruleParamJson);
        preparedStatement.setBytes(6, bitmapBytes);
        preparedStatement.setString(7, calculatorSourceCode);
        // 执行sql
        preparedStatement.execute();

        preparedStatement.close();
        connection.close();


    }

    @Test
    public void insertModelA_rule002() throws IOException, SQLException {
        // 从前端页面收到营销人员定义的规则参数
        String ruleParamJson = "{\n" +
                "  \"rule_id\": \"rule-A-002\",\n" +
                "  \"dynamic_profile_condition1\": {\n" +
                "    \"event_id\": \"X\",\n" +
                "    \"operator\": \">\",\n" +
                "    \"count\": 2\n" +
                "  },\n" +
                "  \"dynamic_profile_condition2\": {\n" +
                "    \"event_id\": \"G\",\n" +
                "    \"property_name\": \"p1\",\n" +
                "    \"operator\": \">\",\n" +
                "    \"max_min_bs\": 2\n" +
                "  },\n" +
                "  \"trigger_condition\": {\n" +
                "    \"event_id\": \"H\",\n" +
                "    \"property_name\": \"p1\",\n" +
                "    \"operator\": \">\",\n" +
                "    \"compare_value\": 3\n" +
                "  }\n" +
                "}";

        // 查询es，圈选出满足规则静态画像条件的人群
        // 假装查完了，得到如下满足条件的用户
        HashSet<Long> preSelectUsers = new HashSet<>(Arrays.asList(1L, 3L, 5L, 9L, 10L, 11L, 12L));
        Roaring64Bitmap bitmap = Roaring64Bitmap.bitmapOf();
        for (Long uid : preSelectUsers) {
            bitmap.add(uid);
        }
        // 序列化bitmap成byte[]
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        DataOutputStream dataOutputStream = new DataOutputStream(byteArrayOutputStream);
        bitmap.serialize(dataOutputStream);
        byte[] bitmapBytes = byteArrayOutputStream.toByteArray();


        // 生成规则元数据，写入规则元数据表
        Connection connection = DriverManager.getConnection("jdbc:mysql://doitedu:3306/doit43", "root", "root");
        // 准备sql模板
        PreparedStatement preparedStatement = connection.prepareStatement("insert into rule_meta values (?,?,?,?,?,?)");
        // 填充value
        preparedStatement.setString(1, "rule-A-002");
        preparedStatement.setString(2, "A");
        preparedStatement.setString(3, "七夕促单");
        preparedStatement.setString(4, "用一点小恩小惠小浪漫，骗那些小青年疯狂消费");
        preparedStatement.setString(5, ruleParamJson);
        preparedStatement.setBytes(6, bitmapBytes);
        // 执行sql
        preparedStatement.execute();

        preparedStatement.close();
        connection.close();


    }


    @Test
    public void insertModelB_rule001() throws IOException, SQLException {

        Connection connection = DriverManager.getConnection("jdbc:mysql://doitedu:3306/doit43", "root", "root");
        // 从前端页面收到营销人员定义的规则参数
        String ruleParamJson = "{\n" +
                "  \"rule_id\":\"rule-B-001\",\n" +
                "  \"model_id\":\"B\",\n" +
                "  \"static_profile_conditions\":[\n" +
                "    {\n" +
                "      \"tag_name\":\"age\",\n" +
                "      \"operator\":\">\",\n" +
                "      \"compare_value\":18\n" +
                "    },\n" +
                "    {\n" +
                "      \"tag_name\":\"gender\",\n" +
                "      \"operator\":\"=\",\n" +
                "      \"compare_value\":\"male\"\n" +
                "    },\n" +
                "    {\n" +
                "      \"tag_name\":\"tag103\",\n" +
                "      \"operator\":\">\",\n" +
                "      \"compare_value\":300\n" +
                "    }\n" +
                "  ],\n" +
                "  \"dynamic_profile_condition\":{\n" +
                "    \"event_id\":\"X\",\n" +
                "    \"operator\":\">=\",\n" +
                "    \"compare_value\":5\n" +
                "  },\n" +
                "  \"trigger_event_id\":\"V\"\n" +
                "}\n";

        // 查询es，圈选出满足规则静态画像条件的人群
        // 假装查完了，得到如下满足条件的用户
        HashSet<Long> preSelectUsers = new HashSet<>(Arrays.asList(1L, 2L, 3L, 4L, 5L));
        Roaring64Bitmap bitmap = Roaring64Bitmap.bitmapOf();
        for (Long uid : preSelectUsers) {
            bitmap.add(uid);
        }
        // 序列化bitmap成byte[]
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        DataOutputStream dataOutputStream = new DataOutputStream(byteArrayOutputStream);
        bitmap.serialize(dataOutputStream);
        byte[] bitmapBytes = byteArrayOutputStream.toByteArray();


        // 根据规则参数中的model_id，去模型元数据表中获取模型运算机源代码
        PreparedStatement pst = connection.prepareStatement("select calculator_source_code from model_meta where model_id = ?");
        pst.setString(1, "B");
        ResultSet resultSet = pst.executeQuery();
        resultSet.next();
        String calculatorSourceCode = resultSet.getString("calculator_source_code");


        // 生成规则元数据，写入规则元数据表

        // 准备sql模板
        PreparedStatement preparedStatement = connection.prepareStatement("insert into rule_meta values (?,?,?,?,?,?,?)");
        // 填充value
        preparedStatement.setString(1, "rule-B-001");
        preparedStatement.setString(2, "B");
        preparedStatement.setString(3, "多易新年报名优惠促销");
        preparedStatement.setString(4, "用江哥的颜值和实力征服你");
        preparedStatement.setString(5, ruleParamJson);
        preparedStatement.setBytes(6, bitmapBytes);
        preparedStatement.setString(7, calculatorSourceCode);
        // 执行sql
        preparedStatement.execute();

        preparedStatement.close();
        connection.close();


    }


    @Test
    public void insertModeC_rule001() throws IOException, SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://doitedu:3306/doit43", "root", "root");
        //Connection connection_doris = DriverManager.getConnection("jdbc:mysql://doitedu:9030/dwd", "root", "root");

        Jedis jedis = new Jedis("doitedu", 6379);

        // 收到页面参数
        String ruleParamJson_bak = "{\n" +
                "  \"rule_id\":\"rule-C-001\",\n" +
                "  \"model_id\": \"C\",\n" +
                "  \"dynamic_profile_condition\":{\n" +
                "    \"event_id\":\"X\",\n" +
                "    \"compare_value\":5,\n" +
                "    \"start_time\":1701360000000,\n" +
                "    \"end_time\":-1\n" +
                "  },\n" +
                "  \"trigger_event_id\":\"Z\"\n" +
                "}";


        String ruleParamJson = "{\n" +
                "  \"rule_id\":\"rule-C-002\",\n" +
                "  \"model_id\": \"C\",\n" +
                "  \"dynamic_profile_condition\":{\n" +
                "    \"event_id\":\"P\",\n" +
                "    \"compare_value\":3,\n" +
                "    \"start_time\":1701360000000,\n" +
                "    \"end_time\":-1\n" +
                "  },\n" +
                "  \"trigger_event_id\":\"G\"\n" +
                "}";

        // 解析参数

        // 根据静态画像条件，圈选人群
        Roaring64Bitmap bitmap = Roaring64Bitmap.bitmapOf(1, 3, 5);
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        DataOutputStream dataOutputStream = new DataOutputStream(byteArrayOutputStream);
        bitmap.serialize(dataOutputStream);
        byte[] bitmapBytes = byteArrayOutputStream.toByteArray();


        // 根据模型id，去取模型运算机源代码
        PreparedStatement pst = connection.prepareStatement("select calculator_source_code from model_meta where model_id = ?");
        pst.setString(1, "C");
        ResultSet resultSet = pst.executeQuery();
        resultSet.next();
        String calculatorSourceCode = resultSet.getString("calculator_source_code");


        // 获取当前时间戳,作为历史段分界点
        long tx = System.currentTimeMillis();

        // 根据参数条件 和 当前时间戳，去doris中查询各用户的跨历史未来的历史段值
//        PreparedStatement preparedStatement = connection_doris.prepareStatement(
//                "select   \n" +
//                        "    user_id,\n" +
//                        "    count(1) as cnt\n" +
//                        "from dwd_events \n" +
//                        "where event_id = 'X'  AND action_time>= ? AND action_time<= ?\n" +
//                        "group by user_id");
//
//        preparedStatement.setLong(1, 1701360000000L);
//        preparedStatement.setLong(2, tx);
//
//        ResultSet rs = preparedStatement.executeQuery();
//        // 将结果写入redis
//        while (rs.next()) {
//            long userId = rs.getLong("user_id");
//            long cnt = rs.getLong("cnt");
//            if (bitmap.contains(userId)) {
//                // 将查询到的历史值，发布到redis
//                jedis.hset("rule-C-001", userId + "", cnt + "");
//            }
//        }

        jedis.hset("rule-C-002", 1 + "", 1 + "");
        jedis.hset("rule-C-002", 3 + "", 2 + "");
        jedis.hset("rule-C-002", 5 + "", 4 + "");



        // 最后，将规则的各种元数据，写入规则元数据表
        // 准备sql模板
        PreparedStatement insertStmt = connection.prepareStatement("insert into rule_meta values (?,?,?,?,?,?,?,?)");
        // 填充value
        insertStmt.setString(1, "rule-C-002");
        insertStmt.setString(2, "C");
        insertStmt.setString(3, "注册促单");
        insertStmt.setString(4, "新用户注册后，用优惠券诱骗它下单");
        insertStmt.setString(5, ruleParamJson);
        insertStmt.setBytes(6, bitmapBytes);
        insertStmt.setString(7, calculatorSourceCode);
        insertStmt.setLong(8, tx);
        // 执行sql
        insertStmt.execute();

        insertStmt.close();
        connection.close();


    }

}
